Skip to main content

Excel Configuration Guide

This guide explains how to configure the Excel import process using JSON configuration files. The configuration structure allows you to define various aspects of Excel processing, including logging, sheet specifications, and feature handling.

Configuration Structure​

The configuration file consists of three main sections:

  • GENERAL_CONFIGS: Global settings that affect the entire import process
  • LOGGING_CONFIGS: Detailed logging configuration options
  • SHEET_SPECS: Array of sheet-specific configurations

General Configurations​

The GENERAL_CONFIGS section controls the overall behavior of the Excel import process.

{
  "FEATURES_AS_HTML_OUTPUT": "true",
  "MINIMIZE_LOGGING": "true",
  "MISSING_DATA_DEFAULT_VALUE": "-",
  "USE_LOGGER": "true",
  "FILE_TYPE": "csv",
  // ... other general configs
}

Key settings include:

  • FEATURES_AS_HTML_OUTPUT: Enables HTML output format for features
  • MISSING_DATA_DEFAULT_VALUE: Default value for missing data fields
  • FILE_TYPE: Output file type (e.g., "csv")
  • EXPORT_DELIMITER: Character used to separate fields in export files
  • CUSTOM_EXCEL_CODE: Enables custom Excel processing code

Logging Configuration​

The LOGGING_CONFIGS section allows fine-grained control over logging behavior for different components of the system.

{
  "APPEND_OUTPUT_CONTROLLER_LOGGING": "true",
  "PROCESSOR_RULE_LOGGING": "true",
  "EXCEL_TABLE_LOAD_LOGGING": "true"
  // ... other logging configs
}

Each logging option can be enabled or disabled independently to help with debugging and monitoring specific components.

Sheet Specifications​

The SHEET_SPECS array contains configurations for individual sheets. Each sheet specification includes:

Basic Sheet Properties​

  • NAME: Identifier for the sheet configuration
  • TARGET_SHEET: Name of the sheet in the Excel file
  • HEADER_ROW_INDEX: Row number containing headers
  • FILE_TYPE: Type of processing to apply ("LAYOUT" or "OFFERS")
  • PATTERN_MATCH: Regular expression pattern for matching

Sheet Configuration Options​

Each sheet has a CONFIG object that can include:

Row Checking​
"RowCheck": {
  "TYPE": "CONDITION",
  "FEATURE": "TopBorder.Thin",
  "LOCATION": {
    "TYPE": "COLUMN",
    "INDEX": "15"
  }
}
Cluster Container Settings​
"ClusterContainer": {
  "CONTAINER_HEIGHT_COVERAGE_DELTA": "1",
  "CLUSTER_CONTAINER_SEARCH_ORDER": ["FIRST", "2", "3", "4", "LAST"]
}
Feature Search Configuration​

The FeatureSearch section defines search criteria for specific features:

"FeatureSearch": {
  "COUNTRY_CODE": {
    "TYPE": "OR_ARRAY",
    "FEATURES": [
      "FONT_COLOR*FF000000*FIRST*15",
      "FONT_COLOR*FF000000*FIRST*17"
    ]
  }
}
Field Mapping​

For OFFERS type sheets, you can define field mappings:

"FieldMapping": {
  "Precio Normal": "PRECIO NORMAL",
  "Precio SE": "PRECIO SE"
}

Advanced Features​

Reusable Formats​

Define common formats that can be referenced throughout the configuration:

"ReusableFormats": {
  "BlueText": {
    "CODE": "6",
    "FORMAT_CODE": "FF0000FF"
  }
}

Field Value Reprocessing​

Configure rules for transforming field values based on conditions:

"FieldValueReprocessing": [
  {
    "FIELD": "OFFER_TYPE",
    "RULES": [
      {
        "VALUE": "TO319",
        "REPLACEMENT_VALUE": "TO309",
        "CONDITIONS": [
          {
            "COUNTRY_CODE": "=MX",
            "INSTRUCTIONS": "~=GRATIS"
          }
        ]
      }
    ]
  }
]

Best Practices​

  1. Always specify a MISSING_DATA_DEFAULT_VALUE to handle null or undefined values
  2. Use meaningful names for sheet configurations
  3. Keep logging configurations organized by component
  4. Test pattern matching expressions before deployment
  5. Document custom field mappings for maintainability

Configuration Examples​

Basic Layout Sheet​

{
  "NAME": "A1",
  "TARGET_SHEET": "PXP",
  "HEADER_ROW_INDEX": "9",
  "FILE_TYPE": "LAYOUT",
  "PATTERN_MATCH": "[A-Za-z][A-Za-z]\\sC\\d\\d\\s\\d\\d\\d\\d",
  "CONFIG": {
    "MAX_COLUMN": 36,
    // ... other config options
  }
}

Basic Offers Sheet​

{
  "NAME": "A2",
  "TARGET_SHEET": "BD",
  "HEADER_ROW_INDEX": "0",
  "FILE_TYPE": "OFFERS",
  "CONFIG": {
    "MAX_COLUMN": 36,
    "FieldMapping": {
      "Precio Normal": "PRECIO NORMAL",
      "Precio SE": "PRECIO SE"
    }
  }
}

Troubleshooting​

Common issues and their solutions:

  1. Missing Headers

    • Verify HEADER_ROW_INDEX is correct
    • Check sheet name matches TARGET_SHEET

  2. Pattern Matching Failures

    • Test PATTERN_MATCH expressions separately
    • Ensure special characters are properly escaped

  3. Field Mapping Issues

    • Confirm source and target field names
    • Check for case sensitivity
    • Verify field exists in source data